package repository

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"reflect"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

type MySQLProvider struct {
	db         *sql.DB
	tableName  string
	entityType reflect.Type
	primaryKey string
	indexes    map[string]bool
}

// NewMySQLProvider 创建MySQL DBProvider实例
func NewMySQLProvider(dsn, tableName string, entityType interface{}) (*MySQLProvider, error) {
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %v", err)
	}

	// 测试连接
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	if err := db.PingContext(ctx); err != nil {
		return nil, fmt.Errorf("failed to ping database: %v", err)
	}

	// 获取类型信息
	t := reflect.TypeOf(entityType)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
	}

	// 解析主键和索引
	primaryKey, indexes := parseTags(t)

	return &MySQLProvider{
		db:         db,
		tableName:  tableName,
		entityType: t,
		primaryKey: primaryKey,
		indexes:    indexes,
	}, nil
}

// Close 关闭数据库连接
func (m *MySQLProvider) Close() error {
	return m.db.Close()
}

// Save 保存实体
func (m *MySQLProvider) Save(ctx context.Context, entity interface{}) error {
	if err := m.validateEntityType(entity); err != nil {
		return err
	}

	val := reflect.ValueOf(entity)
	if val.Kind() == reflect.Ptr {
		val = val.Elem()
	}

	// 检查主键是否存在
	id := val.FieldByName(m.primaryKey).Interface()
	existing, err := m.FindByID(ctx, id)
	if err != nil && !errors.Is(err, ErrNotFound) {
		return err
	}

	if existing == nil {
		return m.insert(ctx, entity)
	}
	return m.update(ctx, entity)
}

// insert 插入新记录
func (m *MySQLProvider) insert(ctx context.Context, entity interface{}) error {
	val := reflect.ValueOf(entity)
	if val.Kind() == reflect.Ptr {
		val = val.Elem()
	}

	// 构建INSERT语句
	columns := []string{}
	placeholders := []string{}
	values := []interface{}{}

	for i := 0; i < m.entityType.NumField(); i++ {
		field := m.entityType.Field(i)
		fieldName := field.Name
		fieldValue := val.Field(i).Interface()

		// 跳过非数据库字段
		if tag := field.Tag.Get("db"); tag == "-" {
			continue
		}

		columns = append(columns, fieldName)
		placeholders = append(placeholders, "?")
		values = append(values, fieldValue)
	}

	query := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)",
		m.tableName,
		strings.Join(columns, ", "),
		strings.Join(placeholders, ", "),
	)

	_, err := m.db.ExecContext(ctx, query, values...)
	return err
}

// update 更新记录
func (m *MySQLProvider) update(ctx context.Context, entity interface{}) error {
	val := reflect.ValueOf(entity)
	if val.Kind() == reflect.Ptr {
		val = val.Elem()
	}

	// 构建UPDATE语句
	sets := []string{}
	values := []interface{}{}
	var id interface{}

	for i := 0; i < m.entityType.NumField(); i++ {
		field := m.entityType.Field(i)
		fieldName := field.Name
		fieldValue := val.Field(i).Interface()

		// 跳过非数据库字段
		if tag := field.Tag.Get("db"); tag == "-" {
			continue
		}

		if fieldName == m.primaryKey {
			id = fieldValue
			continue
		}

		sets = append(sets, fmt.Sprintf("%s = ?", fieldName))
		values = append(values, fieldValue)
	}

	if id == nil {
		return fmt.Errorf("primary key not found")
	}

	query := fmt.Sprintf("UPDATE %s SET %s WHERE %s = ?",
		m.tableName,
		strings.Join(sets, ", "),
		m.primaryKey,
	)

	values = append(values, id)
	_, err := m.db.ExecContext(ctx, query, values...)
	return err
}

// FindByID 通过ID查找
func (m *MySQLProvider) FindByID(ctx context.Context, id interface{}) (interface{}, error) {
	query := fmt.Sprintf("SELECT * FROM %s WHERE %s = ? LIMIT 1", m.tableName, m.primaryKey)
	row := m.db.QueryRowContext(ctx, query, id)

	// 创建新实体
	entity := reflect.New(m.entityType).Interface()

	// 获取字段指针
	columns, pointers, err := m.scanTarget(entity)
	if err != nil {
		return nil, err
	}

	// 确保查询列顺序与结构体字段顺序一致
	orderedPointers := make([]interface{}, len(columns))
	for i, col := range columns {
		if ptr, ok := pointers[col]; ok {
			orderedPointers[i] = ptr
		} else {
			orderedPointers[i] = new(interface{}) // 忽略不匹配的列
		}
	}

	if err := row.Scan(orderedPointers...); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}

	return entity, nil
}

// FindByIndex 通过索引查找
func (m *MySQLProvider) FindByIndex(ctx context.Context, indexName string, indexValue interface{}) ([]interface{}, error) {
	if !m.indexes[indexName] {
		return nil, fmt.Errorf("field %s is not indexed", indexName)
	}

	query := fmt.Sprintf("SELECT * FROM %s WHERE %s = ?", m.tableName, indexName)
	rows, err := m.db.QueryContext(ctx, query, indexValue)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var results []interface{}
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		// 创建新实体
		entity := reflect.New(m.entityType).Interface()

		// 获取字段指针
		_, pointers, err := m.scanTarget(entity)
		if err != nil {
			return nil, err
		}

		// 确保查询列顺序与结构体字段顺序一致
		orderedPointers := make([]interface{}, len(columns))
		for i, col := range columns {
			if ptr, ok := pointers[col]; ok {
				orderedPointers[i] = ptr
			} else {
				orderedPointers[i] = new(interface{}) // 忽略不匹配的列
			}
		}

		if err := rows.Scan(orderedPointers...); err != nil {
			return nil, err
		}

		results = append(results, entity)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return results, nil
}

// FindAll 查找所有记录
func (m *MySQLProvider) FindAll(ctx context.Context) ([]interface{}, error) {
	query := fmt.Sprintf("SELECT * FROM %s", m.tableName)
	rows, err := m.db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var results []interface{}
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		// 创建新实体
		entity := reflect.New(m.entityType).Interface()

		// 获取字段指针
		_, pointers, err := m.scanTarget(entity)
		if err != nil {
			return nil, err
		}

		// 确保查询列顺序与结构体字段顺序一致
		orderedPointers := make([]interface{}, len(columns))
		for i, col := range columns {
			if ptr, ok := pointers[col]; ok {
				orderedPointers[i] = ptr
			} else {
				orderedPointers[i] = new(interface{}) // 忽略不匹配的列
			}
		}

		if err := rows.Scan(orderedPointers...); err != nil {
			return nil, err
		}

		results = append(results, entity)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return results, nil
}

// Delete 删除记录
func (m *MySQLProvider) Delete(ctx context.Context, id interface{}) error {
	query := fmt.Sprintf("DELETE FROM %s WHERE %s = ?", m.tableName, m.primaryKey)
	result, err := m.db.ExecContext(ctx, query, id)
	if err != nil {
		return err
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return err
	}

	if rowsAffected == 0 {
		return ErrNotFound
	}

	return nil
}

// scanTarget 准备扫描目标
func (m *MySQLProvider) scanTarget(entity interface{}) ([]string, map[string]interface{}, error) {
	val := reflect.ValueOf(entity)
	if val.Kind() == reflect.Ptr {
		val = val.Elem()
	}

	pointers := make(map[string]interface{})
	var columns []string

	for i := 0; i < m.entityType.NumField(); i++ {
		field := m.entityType.Field(i)
		fieldName := field.Name

		// 跳过非数据库字段
		if tag := field.Tag.Get("db"); tag == "-" {
			continue
		}

		fieldValue := val.Field(i)
		pointers[fieldName] = fieldValue.Addr().Interface()
		columns = append(columns, fieldName)
	}

	return columns, pointers, nil
}

// validateEntityType 验证实体类型
func (m *MySQLProvider) validateEntityType(entity interface{}) error {
	t := reflect.TypeOf(entity)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
	}

	if t != m.entityType {
		return fmt.Errorf("%w: expected %s, got %s",
			ErrInvalidEntity, m.entityType.Name(), t.Name())
	}
	return nil
}
